package voja.android.mysql;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Vector;

import javax.sql.*;

import android.util.Log;

public class Mysql {
	
	protected static String CONNECTION_HOSTNAME = "localhost";
	protected static int CONNECTION_PORT = 3306;
	protected static String CONNECTION_USERNAME = "root";
	protected static String CONNECTION_PASSWORD = "";
	protected static String CONNECTION_DATABASE = "test";
	
	protected static Mysql _instance;
	
	private Connection _connection;
	
	Mysql() throws Exception {
		String url = "jdbc:mysql://" + Mysql.CONNECTION_HOSTNAME + ":" + Mysql.CONNECTION_PORT + "/";
		String driver = "com.mysql.jdbc.Driver";
		
		Class.forName(driver);
		_connection = DriverManager.getConnection(url+Mysql.CONNECTION_DATABASE, Mysql.CONNECTION_USERNAME, Mysql.CONNECTION_PASSWORD);
	}
	
	protected Vector<HashMap<String, String>> query(String sql) throws Exception {
		Vector<HashMap<String,String>> results = new Vector<HashMap<String,String>>();
		
		Statement q = _connection.createStatement();
		ResultSet rs = q.executeQuery(sql);
		HashMap<String,String> row;
		int i=0;
		ResultSetMetaData meta = rs.getMetaData();
		int cnt = meta.getColumnCount();
		rs.last();
		
		if(rs.first()) {
			do {
				row = new HashMap<String,String>();
				for(i=1; i<=cnt; i++) {
					row.put(meta.getColumnName(i).toString(), rs.getString(i));
				}
				results.add(row);
			}while(rs.next());
		}
		rs.close();
		return results;
	}
	
	protected static Mysql _getInstance() throws Exception {
		if(Mysql._instance == null) {
			Mysql._instance = new Mysql();
		}
		return Mysql._instance;
	}
	
	public static void setCredentials(String host, String port, String db, String user, String pass) {
		Mysql.CONNECTION_HOSTNAME = host;
		Mysql.CONNECTION_PORT = Integer.parseInt(port);
		Mysql.CONNECTION_DATABASE = db;
		Mysql.CONNECTION_USERNAME = user;
		Mysql.CONNECTION_PASSWORD = pass;
	}
	
	public static void updateConnection(String host, String port, String db, String user, String pass) throws Exception {
		Mysql.setCredentials(host, port, db, user, pass);
		Mysql._instance = new Mysql();
	}
	
	public static String select(String sql) throws Exception {
		String retval = "";
		Mysql db = Mysql._getInstance();
		Log.v("mysql", "query: "+sql);
		Vector<HashMap<String, String>> r = db.query(sql);
		Log.v("mysql", "result: "+r.size());
		if(r.size()>0) {
			HashMap<String,String> row;
			
			for(int i=0; i<r.size(); i++) {
				row = r.elementAt(i);
				for(Map.Entry<String,String> entry : row.entrySet()) {
					retval += entry.getKey() + ": " + entry.getValue() + "\n";
				}
				retval += "-----\n\n";
			}
			
		}else{
			retval = "No results";
		}
		Log.v("mysql", "Result: "+retval);
		return retval;
	}
	
	public static void close() throws Exception {
		Mysql._getInstance()._connection.close();
	}
}
